#!/bin/bash
#设置任务异常退出
set -eo pipefail
dt='{{ execution_date | cst_ds }}'
name='达成率-温台代理区t-1揽收数据'
# 在退出时清理文件
trap "rm -f /tmp/${name}_${dt}.zip /tmp/${name}_${dt}.csv && echo 'removed tmp files'" EXIT
# 创建txt文件
touch /tmp/${name}_${dt}.txt
#将中文字段名输入文件顶部
#将查询结果追加到文件中
{
  echo -e "运单号\t寄件网点\t寄件代理区\t寄件市\t寄件区县\t揽收时间\t入仓时间\t始发转运中心\t始发转运中心到车时间\t支线任务单号\t干线任务单号\t始发转运到件时间\t始发转运发件时间\t干线任务号（中心-中心）\t目的转运中心\t签收网点\t签收代理区\t签收市\t签收区县\t签收时间">/tmp/${name}_${dt}.txt;
  spark-sql --driver-memory 8G --num-executors 10 --executor-memory 12g --executor-cores 4  --conf spark.executor.memoryOverhead=4g --conf spark.driver.maxResultSize=3g -S -e "
with prescription_reach as (
         select waybill_no
              , start_pick_network_name
              , start_subordinate_agent_code
              , start_subordinate_agent_name
              , start_city_desc
              , start_area_desc
              , taking_time
              , warehous_time
              , start_center_name
              , start_centersend_arrival_time
              , arrival_task_no
              , departure_task
              , start_centerarriva_time
              , start_centersend_time
              , departure_line_task
              , aim_center_name
              , aim_centerarrival_departure_time
              , aim_centersend_time
              , sign_network_name
              , sign_subordinate_agent_name
              , sign_city_desc
              , sign_area_desc
              , districtprescription
              , pres_sign_time
              , cainiao_is_platform_pres_standard
              , is_pres_standard
         from jms_dm.dm_waybill_prescription_reach_details_platform_dt
         where dt = '{{ execution_date | cst_ds }}'
           and start_subordinate_agent_code = '330001'
     )
select nvl(waybill_no, ' ')                                                  as waybill_no
     , nvl(start_pick_network_name, ' ')                                     as start_pick_network_name
     , nvl(start_subordinate_agent_name, ' ')                                as start_subordinate_agent_name
     , nvl(start_city_desc, ' ')                                             as start_city_desc
     , nvl(start_area_desc, ' ')                                             as start_area_desc
     , nvl(date_format(taking_time, 'yyyy-MM-dd HH:mm:ss'), ' ')             as taking_time
     , nvl(date_format(warehous_time, 'yyyy-MM-dd HH:mm:ss'), ' ')           as warehous_time
     , nvl(start_center_name, ' ')                                           as start_center_name
     , nvl(date_format(aa.start_centersend_arrival_time, 'yyyy-MM-dd HH:mm:ss'),' ') as start_centersend_arrival_time
     , nvl(arrival_task_no, ' ')                                             as arrival_task_no
     , nvl(departure_task, ' ')                                              as departure_task
     , nvl(date_format(start_centerarriva_time, 'yyyy-MM-dd HH:mm:ss'), ' ') as start_centerarriva_time
     , nvl(date_format(start_centersend_time, 'yyyy-MM-dd HH:mm:ss'), ' ')   as start_centersend_time
     , nvl(departure_line_task, ' ')                                         as departure_line_task
     , nvl(aim_center_name, ' ')                                             as aim_center_name
     , nvl(sign_network_name, ' ')                                           as sign_network_name
     , nvl(sign_subordinate_agent_name, ' ')                                 as sign_subordinate_agent_name
     , nvl(sign_city_desc, ' ')                                              as sign_city_desc
     , nvl(sign_area_desc, ' ')                                              as sign_area_desc
     , nvl(date_format(pres_sign_time, 'yyyy-MM-dd HH:mm:ss') ,' ')          as pres_sign_time
from prescription_reach aa
;
 " >>/tmp/${name}_${dt}.txt
}

# 替换分隔符，并输出csv
cat /tmp/${name}_${dt}.txt | tr -s '\t' ',' > /tmp/${name}_${dt}.csv
# 压缩并删除 csv 文件
zip -jrm /tmp/${name}_${dt}.zip /tmp/${name}_${dt}.csv
# 上传文件
/usr/local/python3/bin/python3  /usr/local/airflow/dags/utils/operators/upload_oss.py ${name}/${name}_${dt}.zip /tmp/${name}_${dt}.zip
# zip 文件将在进程退出后被自动清理

#声明文件大小
data_size=$(ls -lh /tmp/${name}_${dt}.zip | awk '{print $5}')
#打印文件信息
Message="${name}_${dt}.zip 上传成功 大小: $data_size"
#配置钉钉告警
curl 'https://oapi.dingtalk.com/robot/send?access_token=816a8f35fd84432ffa6bff9d7714abc322628bb4a8ded86908550d2335aba613' \
   -H 'Content-Type: application/json' \
   -d "
  {\"msgtype\": \"text\",
    \"text\": {
        \"content\": \"监控内容:$Message\"
     }
  }" || true
rm -f  /tmp/${name}_${dt}.txt